
# oracle_unique_columns.py

import cx_Oracle
import codecs
import string
import re
from pepe.glib import drop_vowels

zf2 = lambda x: string.zfill(str(x), 2)

def main( tables
        , conStr={'usr':'', 'pwd':'', 'dsn':''}
        , startNum=1):
    ''' make a_copy of table and rename columns to cname_tcnt
    '''
    con = cx_Oracle.connect(conStr['usr'], conStr['pwd'], conStr['dsn'])
    cur = con.cursor()
    
    # initialize count used to append after column name
    cnt = startNum
    
    for st in tables:

        #print st
        s, t = string.split(st, '.')
        #print zf2(cnt), s, t
        print "%s %s.a_copy_%s" % (zf2(cnt), s, t)

        # drop target a_copy table if exists
        try:
            cur.execute("drop table %s.a_copy_%s" % (s, t))
        except:
            pass

        # create target a_copy table
        q = '''  create table %s.a_copy_%s as
                        (
                 select *
                   from %s.%s)''' % (s, t, s, t)
        cur.execute(q)
        
        q = """
                 select * from ALL_TAB_COLUMNS
                  where OWNER = '%s'
                    and TABLE_NAME = '%s'
                  """ % (s, t)
        #print q
        cur.execute(q)
        
        results = cur.fetchall()[:]
        # rename each target column to cname_zf2
        for r in results:
            name_changed = '_'
            #print r[2]
            co = r[2]  # column original name
            cn = co[:] # column new name
            #c = 'KOD_OBLAST'

            # Oracle has naming limitation of 30 characters
            if len(co) > 27: 
                cn = drop_vowels(co, limit=27)
                # if not enough, drop all vowels 
                if len(cn) > 26: 
                    cn = re.sub('[AIEOU]', '', cn)
                print '\t\t', co, '->',cn
                # if column name was changed, indicate this via
                # x<column name>_NN
                # instead of default for unchanged columns
                # <column name>_NN
                q = '''
                          alter table %s.a_copy_%s
                         rename column %s TO x%s_%s
                '''
            else:
                q = '''
                          alter table %s.a_copy_%s
                         rename column %s TO %s_%s
                '''
            cur.execute(q % (s, t, co, cn, zf2(cnt)))

        # increase count used to append after column name
        cnt += 1
    
    cur.close()
    con.close()

if 0: # testing routine (and how to use it in external script example)

    import cx_Oracle
    import getpass
    import pepe.p_sql.oracle_unique_columns as uc

    ip = '127.0.0.1'    # tunneled to remote server
    port = 1522         # shifted to tunneled 1522, on 1521 is local XE instance
    #port = raw_input('Port: ')

    sid = 'dtrslab3'
    dsn_tns = cx_Oracle.makedsn(ip, port, sid)

    usr = 'vpresovsky'
    #usr = raw_input('Username: ')

    pwd = '<...>'
    #pwd = getpass.getpass()

    conString = {'usr': usr, 'pwd': pwd, 'dsn': dsn_tns}
    #uc.main(['GIS_RELEASE.CIS_STAVZADOSTI'], conString, startNum=18)
    uc.main(['SPS.SUBJEKT', 'SPS.OBJEKTSPS', 'SPS.DOTCENE_SUBJEKTY'], conString, startNum=19)


if __name__== '__main__':
    """ """
    import doctest
    doctest.testmod()
